﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using model;
using System.Data;

namespace Dal
{
    public class StudentDal
    {
        public void ZhuCe(Student student)
        {
            string sql = string.Format("insert into Student values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',' ')", student.Sno, student.Spwd, student.Sname, student.Ssex, student.Smajor, student.Sclass, student.Sbirth, student.Go);
            DbSqlHelper.ExecuteSql(sql);
        }
        public int Login(string sno, string spwd)
        {
            string sql = "select * from Student where sno='" + sno + "'and spwd='"+spwd+"'";
            return DbSqlHelper.LoginSql(sql);
        }
        public string Login(string sno)
        {
            string str = "sname";
            string sql = "select * from Student Where sno='" + sno + "'";
            return DbSqlHelper.UserNameSql(sql,str);
        }
        public Student GetSingle(string sno)
        {
            string sql = "select * from student where sno='" + sno+"'";
            DataSet ds= DbSqlHelper.Query(sql);
            Student student = new Student();
            DataRow row = ds.Tables[0].Rows[0];
            student.Sno = row["sno"].ToString();
            student.Spwd = row["spwd"].ToString();
            student.Sname = row["sname"].ToString();
            student.Ssex = row["ssex"].ToString();
            student.Smajor = row["major"].ToString();
            student.Sclass = row["class"].ToString();
            student.Sbirth = Convert.ToDateTime(row["birth"]);
            student.Go = row["go"].ToString();
            student.Gowhere = row["gowhere"].ToString();
            return student;
        }
        public void Update(Student student)
        {
            string sql = string.Format("update Student set sname='{0}',ssex='{1}',major='{2}',class='{3}',birth='{4}' where sno='{5}'",student.Sname,student.Ssex,student.Smajor,student.Sclass,student.Sbirth,student.Sno);
            DbSqlHelper.ExecuteSql(sql);
        }
        public void PwdUpdate(string spwd,string sno)
        {
            string sql = string.Format("update Student set spwd='{0}' where sno='{1}'",spwd,sno);
            DbSqlHelper.ExecuteSql(sql);
        }
        public void UpdateGo(Student st)
        {
            string sql = string.Format("update Student set go='{0}',gowhere='{1}' where sno='{2}'",st.Go,st.Gowhere,st.Sno);
            DbSqlHelper.ExecuteSql(sql);
        }
        public List<Student> GetList(string name)
        {
            StringBuilder sql = new StringBuilder("select * from Student where 1=1 ");
            //根据查询条件来拼接sql语句
            if (string.IsNullOrEmpty(name) == false)
                sql.Append(" and sname like '%" + name + "%'");
            DataSet ds = DbSqlHelper.Query(sql.ToString());
            List<Student > datas = new List<Student>();
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                Student st = new Student();
                st.Sno = row["sno"].ToString();
                st.Sname = row["sname"].ToString();
                st.Ssex = row["ssex"].ToString();
                st.Smajor = row["major"].ToString();
                st.Go = row["go"].ToString();
                st.Gowhere = row["gowhere"].ToString();
                datas.Add(st);
            }
            return datas;
        }
        public void Delete(string sno)
        {
            string sql = "delete from Student where sno='" + sno + "'";
            DbSqlHelper.ExecuteSql(sql);
        }
        public DataTable getStudents(string keyword)
        {
            string sql = "select * from Student";
            if (!string.IsNullOrEmpty(keyword))
            {
                sql += " where sno like '%" + keyword + "%' or sname like '%" + keyword + "%' or major like '%" + keyword + "%' or gowhere like '%"+keyword+"%'";
            }
            return DbSqlHelper.Query(sql).Tables[0];
        }
        public DataTable getStudents(string keyword,int i)
        {
            string sql = "select * from Student";
            if (!string.IsNullOrEmpty(keyword))
            {
                sql += " where sno like '%" + keyword + "%' or sname like '%" + keyword + "%'";
            }
            return DbSqlHelper.Query(sql).Tables[0];
        }
        public int enable(string sno)
        {
            string sql = "select paraval from properties where ParaNo='" + sno + "' and ParaType='StudentEnable'";
            DataTable dt = DbSqlHelper.Query(sql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                string sql2 = "delete from properties where ParaNo='" + sno + "' and ParaType='StudentEnable'";
                return DbSqlHelper.ExecuteSql(sql2);
            }
            else
            {
                string sql3 = "insert into properties values('StudentEnable','" + sno + "','0')";
                return DbSqlHelper.ExecuteSql(sql3);
            }
        }
        public int getisok(string sno)
        {
            string sql = "select paraval from properties where ParaNo='"+sno+"' and ParaType='StudentEnable'";
            DataTable dt = DbSqlHelper.Query(sql).Tables[0];
            if (dt.Rows.Count > 0)
                return 0;
            return 1;
        }
        public DataTable GetDataTable(string sql)
        {
            return DbSqlHelper.Query(sql).Tables[0];
        }
        public int ExcecuteSql(string sql)
        {
            return DbSqlHelper.ExecuteSql(sql);
        }
    }
}
